package com.leexuan.ordersystem.db;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.leexuan.ordersystem.beans.Dsort;

public class DsortDB extends DB {

	private String ADD_DSORT = "INSERT INTO dsort VALUES (?)";
	private String DEL_DSORT = "DELETE FROM dsort WHERE dsid=?";
	private String UPDATE_DSORT = "UPDATE dsort SET name=? WHERE dsid=?";
	private String GET_DSORTS = "SELECT * FROM dsort";
	private String GET_DSORT_BY_ID = "SELECT * FROM dsort WHERE dsid=?";

	public Dsort getDsort(int sid) {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Dsort sort = new Dsort();
		try {
			pstmt = conn.prepareStatement(GET_DSORT_BY_ID);
			pstmt.setInt(1, sid);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				sort.setDsid(rs.getInt("dsid"));
				sort.setName(rs.getString("name"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;
				conn.close();
				conn = null;
			} catch (SQLException ex1) {
				ex1.printStackTrace();
			}
		}
		return sort;
	}

	public boolean addDsort(Dsort st) {
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(ADD_DSORT);

			System.out.print(st.getName());

			pstmt.setString(1, st.getName());

			return pstmt.execute();

		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			try {
				if (pstmt != null) {
					pstmt.close();
					pstmt = null;
				}
			} catch (SQLException ex1) {
				ex1.printStackTrace();
			}
		}
	}

	public boolean updateDsort(Dsort st, int sid) {
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(UPDATE_DSORT);

			pstmt.setString(1, st.getName());
			pstmt.setInt(2, sid);

			return pstmt.execute();

		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			try {
				if (pstmt != null) {
					pstmt.close();
					pstmt = null;
				}
			} catch (SQLException ex1) {
				ex1.printStackTrace();
			}
		}
	}

	public boolean delDsort(int sid) {
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(DEL_DSORT);

			pstmt.setInt(1, sid);

			return pstmt.execute();

		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			try {
				if (pstmt != null) {
					pstmt.close();
					pstmt = null;
				}
			} catch (SQLException ex1) {
				ex1.printStackTrace();
			}
		}
	}

	public ArrayList getDsorts() throws SQLException {

		PreparedStatement pstmt = null;
		ResultSet rs = null;

		ArrayList sorts = new ArrayList();

		try {
			pstmt = conn.prepareStatement(GET_DSORTS, ResultSet.TYPE_SCROLL_SENSITIVE,
					ResultSet.CONCUR_UPDATABLE);
			rs = pstmt.executeQuery();

			while (rs.next()) {

				Dsort sort = new Dsort();

				sort.setDsid(rs.getInt("dsid"));
				sort.setName(rs.getString("name"));
				sorts.add(sort);

			}

		} catch (SQLException ex) {
			ex.printStackTrace();

			throw new SQLException("SQLException on : DAO");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;

			} catch (SQLException ex1) {
				ex1.printStackTrace();
			}

		}
		return sorts;
	}
}
